/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Marks;

import Admission.AdmissionBean;
import Course.CourseBean;
import ExamType.ExamTypeBean;
import Student.StudentBean;
import Subject.SubjectBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Stack;
import java.util.Vector;

/**
 *
 * @author Mukesh
 */
public class Query {
    static Connection con=null;
    public static int addMarks(Vector list) {
        int i[];
        int k=0;
        try {
            Connection con = DBConnection.Connection.getConnection();
            Iterator it = list.iterator();

            PreparedStatement pre = con.prepareStatement("insert into marks values(marksid.nextval,?,?,?,?,sysdate,?,?)");
            
            while (it.hasNext()) {
                MarksBean mb = (MarksBean) it.next();
                pre.setInt(1, mb.getAdmissionid());
                pre.setInt(2, mb.getSubid());
                pre.setInt(3, mb.getExamtypeid());
                pre.setInt(4, mb.getMarks());
                pre.setInt(5, mb.getUid());
                pre.setInt(6, mb.getSem()); 
                pre.addBatch();
                Statement st=con.createStatement();
                st.execute("delete from marks where admission_id="+mb.getAdmissionid()+" and subject_id="+mb.getSubid()+" and exam_type_id="+mb.getExamtypeid());
            }
            i = pre.executeBatch();
            if (i.length != 0) {
               k=i.length;
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return k;
    }
    
    public static HashMap getMarksReport(int admissionid,int subid) {
        HashMap hm;
        hm = new HashMap();
        Vector<SubjectBean> sblist=new Vector<SubjectBean>();
        Vector<MarksBean> mblist=new Vector<MarksBean>();
        try {
            Connection con = DBConnection.Connection.getConnection();

            PreparedStatement pre = con.prepareStatement("select * from marks m left join (select subject_id,total_marks,min_marks,theory_practical,weightage from subject) s on m.subject_id=s.subject_id where m.admission_id=?");
            pre.setInt(1, admissionid);
           // pre.setInt(2, subid);
            ResultSet rs=pre.executeQuery();
            while (rs.next()) {
            MarksBean mb=new MarksBean(); 
            SubjectBean sb=new SubjectBean();
            mb.setMarksid(rs.getInt("MARKS_ID"));
            mb.setAdmissionid(rs.getInt("ADMISSION_ID"));
            mb.setSubid(rs.getInt("subject_id"));
            mb.setExamtypeid(rs.getInt("exam_type_id"));
            mb.setMarks(rs.getInt("marks"));
            mb.setEntrydate(rs.getDate("entry_date"));
            mb.setUid(rs.getInt("user_id"));
            mb.setSem(rs.getInt("semester"));
            sb.setSubjectId(rs.getInt("subject_id"));
            sb.setTotalMarks(rs.getInt("total_marks"));
            sb.setMinMarks(rs.getInt("min_marks"));
            sb.setTheoryPractical(rs.getInt("THEORY_PRACTICAL"));
            sb.setWeightaege(rs.getInt("WEIGHTAGE"));
            sblist.add(sb);
            mblist.add(mb);
            }
            hm.put("sblist", sblist);
            hm.put("mblist", mblist);
            
        } catch (Exception e) {
            System.out.println(e);
        }
        return hm;
    }
public static Vector viewResult(String email)throws SQLException{
    Vector<MarksBean> list=new Vector<MarksBean>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from marks m left join (select subject_id,SUBJECT_NAME,COURSE_STREAM,total_marks,min_marks,theory_practical,weightage from subject) s on m.subject_id=s.subject_id left join admission a on a.admission_id=m.admission_id left join course c on c.course_id=a.course_id left join EXAM_TYPE et on et.EXAM_TYPE_ID=m.EXAM_TYPE_ID where a.student_email=?");
        pre.setString(1, email);
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            MarksBean mb=new MarksBean();
            SubjectBean sb=new SubjectBean();
            CourseBean cb=new CourseBean();
            AdmissionBean ab=new AdmissionBean();
            ExamTypeBean etb=new ExamTypeBean();
            mb.setMarksid(rs.getInt(1));
            mb.setAdmissionid(rs.getInt(2));
            mb.setSubid(rs.getInt(3));
            mb.setExamtypeid(rs.getInt(4));
            mb.setMarks(rs.getInt(5));
            mb.setEntrydate(rs.getDate(6));
            mb.setUid(rs.getInt(7));
            mb.setSem(rs.getInt(8));
            
            sb.setSubjectId(rs.getInt(9));
            sb.setSubjectName(rs.getString(10));
            sb.setCourseStream(rs.getString(11));
            sb.setTotalMarks(rs.getInt(12));
            sb.setMinMarks(rs.getInt(13));
            sb.setTheoryPractical(rs.getInt(14));
            sb.setWeightaege(rs.getInt(15));
            mb.setSubjectBean(sb);
            ab.setAddmissionId(rs.getInt(16));
            ab.setStudentEmail(rs.getString(17));
            ab.setStudentMobile(rs.getString(18));
            ab.setRollNumber(rs.getString(19));
            ab.setStudentSession(rs.getString(20));
            ab.setCourseId(rs.getInt(21));
            ab.setApplicationNo(rs.getString(22));
            ab.setAdmissionDate(rs.getDate(23));
            ab.setEntryDate(rs.getDate(24));
            ab.setEntryEmail(rs.getString(25));
            ab.setSemester(rs.getInt(26));
            mb.setAdmissionBean(ab);
            cb.setCourseId(rs.getInt(27));
            cb.setCourseName(rs.getString(28));
            cb.setCourseType(rs.getInt(29));
            cb.setCoursePeriod(rs.getInt(30));
            cb.setStream(rs.getString(31));
            cb.setEntryDate(rs.getDate(32));
            mb.setCourseBean(cb);
            etb.setExamTypeId(rs.getInt(33));
            etb.setExamTypeName(rs.getString(34));
            etb.setEntryDate(rs.getDate(35));
            etb.setWeightage(rs.getInt(36));
            mb.setExamTypeBean(etb);
            list.add(mb);
        }
        
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}

}
